package com.esunrising.clickhouse.controller;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.esunrising.clickhouse.dto.HistogramChart;
import com.esunrising.clickhouse.dto.ProjectReceive;
import com.esunrising.clickhouse.dto.emp;
import com.esunrising.clickhouse.utils.ClickhouseUtil;
import org.springframework.web.bind.annotation.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

@RestController
@RequestMapping("/receive")
public class ProjectReceiveController {

    @GetMapping("/all")
    public JSONObject getAll(String param){
        JSONObject result = new JSONObject();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = ClickhouseUtil.getConnection();

            if(conn != null){
                String sql = "select id, project, year, receive_money, claim_money, invoice_money from project_receive ";
                if(param != null && param.trim().length() > 0){
                    sql +=  " where project like ? " +
                            " or toString(year) like ? " +
                            " or toString(receive_money) like ? " +
                            " or toString(claim_money) like ? " +
                            " or toString(invoice_money) like ? ";
                }
                pstmt = conn.prepareStatement(sql);
                if(param != null && param.trim().length() > 0) {
                    String paramLike = "%"+param.trim()+"%";
                    pstmt.setString(1, paramLike);
                    pstmt.setString(2, paramLike);
                    pstmt.setString(3, paramLike);
                    pstmt.setString(4, paramLike);
                    pstmt.setString(5, paramLike);
                }

                rs = pstmt.executeQuery();

                List<ProjectReceive> projectReceiveList = new ArrayList<>();
                while (rs.next()){
                    projectReceiveList.add(
                            new ProjectReceive(rs.getString("id"),
                                    rs.getString("project"),
                                    rs.getInt("year"),
                                    rs.getDouble("receive_money"),
                                    rs.getDouble("claim_money"),
                                    rs.getDouble("invoice_money")
                            )
                    );
                }

                result.put("code", 200);
                result.put("msg", "查询成功");
                result.put("size", projectReceiveList.size());
                result.put("data", projectReceiveList);
            }else{
                result.put("code", 500);
                result.put("msg", "数据库连接失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
            result.put("code", 500);
            result.put("msg", "发生错误："+e.getMessage());
        } finally {
            ClickhouseUtil.close(rs, pstmt, conn);
        }
        return result;
    }

    @GetMapping("/oneById")
    public JSONObject getOneById(String param){
        JSONObject result = new JSONObject();
        if(param == null || param.trim().length() == 0) {
            result.put("code", 300);
            result.put("msg", "参数param不能为空");
            return result;
        }
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = ClickhouseUtil.getConnection();

            if(conn != null){
                String sql = "select id, project, year, receive_money, claim_money, invoice_money from project_receive " +
                        " where id = ? limit 1";

                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, param.trim());

                rs = pstmt.executeQuery();

                ProjectReceive projectReceive = new ProjectReceive();
                while (rs.next()){
                    projectReceive.setId(rs.getString("id"));
                    projectReceive.setProject(rs.getString("project"));
                    projectReceive.setYear(rs.getInt("year"));
                    projectReceive.setReceiveMoney(rs.getDouble("receive_money"));
                    projectReceive.setClaimMoney(rs.getDouble("claim_money"));
                    projectReceive.setInvoiceMoney(rs.getDouble("invoice_money"));
                }
                if(projectReceive == null){
                    result.put("code", 301);
                    result.put("msg", "未查询到指定数据");
                }else {
                    result.put("code", 200);
                    result.put("msg", "查询成功");
                    result.put("data", projectReceive);
                }
            }else{
                result.put("code", 500);
                result.put("msg", "数据库连接失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
            result.put("code", 500);
            result.put("msg", "发生错误："+e.getMessage());
        } finally {
            ClickhouseUtil.close(rs, pstmt, conn);
        }
        return result;
    }

    @PostMapping("/save")
    public JSONObject insert(@RequestBody List<ProjectReceive> projectReceiveList){
        JSONObject result = new JSONObject();
        if(projectReceiveList == null || projectReceiveList.size() == 0) {
            result.put("code", 300);
            result.put("msg", "参数不能为空");
            return result;
        }
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = ClickhouseUtil.getConnection();

            if(conn != null){
                String sql = "insert into project_receive(id, project, year, receive_money, claim_money, invoice_money) " +
                        "values( generateUUIDv4(), ?, ?, ?, ?, ?)";
                pstmt = conn.prepareStatement(sql);
                for (ProjectReceive projectReceive : projectReceiveList) {
                    pstmt.setString(1, projectReceive.getProject());
                    pstmt.setInt(2, projectReceive.getYear());
                    pstmt.setDouble(3, projectReceive.getReceiveMoney());
                    pstmt.setDouble(4, projectReceive.getClaimMoney());
                    pstmt.setDouble(5, projectReceive.getInvoiceMoney());
                    boolean res = pstmt.execute();
                }

                result.put("code", 200);
                result.put("msg", "成功添加" + projectReceiveList.size()+"行");
            }else{
                result.put("code", 500);
                result.put("msg", "数据库连接失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
            result.put("code", 500);
            result.put("msg", "发生错误："+e.getMessage());
        } finally {
            ClickhouseUtil.close(pstmt, conn);
        }
        return result;
    }

    @PostMapping("/update")
    public JSONObject update(@RequestBody List<ProjectReceive> projectReceiveList){
        JSONObject result = new JSONObject();
        if(projectReceiveList == null || projectReceiveList.size() == 0) {
            result.put("code", 300);
            result.put("msg", "参数不能为空");
            return result;
        }
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = ClickhouseUtil.getConnection();

            if(conn != null){
                String sql = "alter table project_receive " +
                        " update project = ?, year = ?, receive_money = ?, claim_money = ?, invoice_money = ? " +
                        " where id = ? ";
                pstmt = conn.prepareStatement(sql);
                for (ProjectReceive projectReceive : projectReceiveList) {
                    pstmt.setString(1, projectReceive.getProject());
                    pstmt.setInt(2, projectReceive.getYear());
                    pstmt.setDouble(3, projectReceive.getReceiveMoney());
                    pstmt.setDouble(4, projectReceive.getClaimMoney());
                    pstmt.setDouble(5, projectReceive.getInvoiceMoney());
                    pstmt.setString(6, projectReceive.getId());
                    boolean res = pstmt.execute();
                }

                result.put("code", 200);
                result.put("msg", "更新成功");
            }else{
                result.put("code", 500);
                result.put("msg", "数据库连接失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
            result.put("code", 500);
            result.put("msg", "发生错误："+e.getMessage());
        } finally {
            ClickhouseUtil.close(pstmt, conn);
        }
        return result;
    }

    @PostMapping("/delete")
    public JSONObject delete(@RequestBody String param){
        JSONObject result = new JSONObject();
        String idStr;
        if(param == null || param.trim().length() == 0) {
            result.put("code", 300);
            result.put("msg", "参数不能为空");
            return result;
        }else {
            JSONObject jo = JSONObject.parseObject(param);
            idStr = jo.getString("idStr");
            if(idStr == null || idStr.trim().length() == 0){
                result.put("code", 303);
                result.put("msg", "参数错误");
                return result;
            }
        }
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = ClickhouseUtil.getConnection();

            if(conn != null){

                String sql = "alter table project_receive " +
                        " delete where id = ? ";
                pstmt = conn.prepareStatement(sql);

                String[] idArr = idStr.split(",");
                for (String id : idArr) {
                    if(id != null && id.length() > 0) {
                        pstmt.setString(1, id);
                        pstmt.execute();
                    }
                }
                result.put("code", 200);
                result.put("msg", "删除成功");
            }else{
                result.put("code", 500);
                result.put("msg", "数据库连接失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
            result.put("code", 500);
            result.put("msg", "发生错误："+e.getMessage());
        } finally {
            ClickhouseUtil.close(pstmt, conn);
        }
        return result;
    }



    @GetMapping("/chart")
    public JSONObject chart(){
        JSONObject result = new JSONObject();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = ClickhouseUtil.getConnection();

            if(conn != null){
                String sql = "select year, project, sum(receive_money) value from project_receive group by year, project order by year, project";
                pstmt = conn.prepareStatement(sql);

                rs = pstmt.executeQuery();

                List<HistogramChart> histogramChartList = new ArrayList<>();
                while (rs.next()){
                    histogramChartList.add(
                            new HistogramChart(rs.getString("year"),
                                    rs.getString("project"),
                                    rs.getDouble("value")
                            )
                    );
                }
                JSONObject data = new JSONObject();
                JSONArray xAxis = new JSONArray();
                for (HistogramChart histogramChart : histogramChartList) {
                    JSONObject oneXAxis = new JSONObject();
                    oneXAxis.put("value", histogramChart.getxAxis());
                    if(!xAxis.contains(oneXAxis)){
                        xAxis.add(oneXAxis);
                    }
                }
                JSONArray series = new JSONArray();
                for (HistogramChart histogramChart : histogramChartList) {
                    JSONObject oneSeries = new JSONObject();
                    oneSeries.put("name", histogramChart.getSeries());
                    JSONArray yAxis = new JSONArray();

                    for (Object xAxi : xAxis) {
                        JSONObject oneXAxis = (JSONObject) xAxi;
                        String year = oneXAxis.getString("value");


                        JSONObject oneYAxis = new JSONObject();
                        Double pNum = 0.0d;
                        for (HistogramChart h1 : histogramChartList) {
                            if(h1.getxAxis().equals(year)&&h1.getSeries().equals(histogramChart.getSeries())){
                                pNum = h1.getValue();
                            }
                        }
                        oneYAxis.put("value", pNum);
                        yAxis.add(oneYAxis);
                    }

                    oneSeries.put("yAxis", yAxis);

                    if (!series.contains(oneSeries)){
                        series.add(oneSeries);
                    }
                }

                data.put("xAxis", xAxis);
                data.put("series", series);
                result.put("code", 200);
                result.put("msg", "查询成功");
                result.put("data", data);
            }else{
                result.put("code", 500);
                result.put("msg", "数据库连接失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
            result.put("code", 500);
            result.put("msg", "发生错误："+e.getMessage());
        } finally {
            ClickhouseUtil.close(rs, pstmt, conn);
        }
        return result;
    }
}
